{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "正在翻译第1个单词\n",
      "building\n",
      "n. 建筑；建筑物\n",
      "v. 建筑；建立；增加（build的ing形式）\n",
      "\n",
      "正在翻译第2个单词\n",
      "outdoor\n",
      "adj. 户外的；露天的；野外的（等于out-of-door）\n",
      "\n",
      "正在翻译第3个单词\n",
      "street\n",
      "n. 街道\n",
      "adj. 街道的\n",
      "n. (Street)人名；(英、葡)斯特里特；(德)施特雷特\n",
      "\n",
      "正在翻译第4个单词\n",
      "city\n",
      "n. 城市，都市\n",
      "adj. 城市的；都会的\n",
      "\n",
      "正在翻译第5个单词\n",
      "people\n",
      "n. 人；人类；民族；公民\n",
      "vt. 居住于；使住满人\n",
      "\n",
      "正在翻译第6个单词\n",
      "busy\n",
      "adj. 忙碌的；热闹的；正被占用的\n",
      "vt. 使忙于\n",
      "n. (Busy)人名；(匈)布希；(法)比西\n",
      "\n",
      "正在翻译第7个单词\n",
      "night\n",
      "n. 夜晚，晚上；黑暗，黑夜\n",
      "adj. 夜晚的，夜间的\n",
      "\n",
      "正在翻译第8个单词\n",
      "ride\n",
      "n. 骑；乘坐；交通工具；可供骑行的路；（乘坐汽车等的）旅行；乘骑；（乘车或骑车的）短途旅程；供乘骑的游乐设施\n",
      "vi. 骑马；乘车；依靠；漂浮\n",
      "vt. 骑；乘；控制；（骑马、自行车等）穿越；搭乘；飘浮\n",
      "n. （英）赖德（人名）；（法、葡）里德（人名）\n",
      "\n",
      "正在翻译第9个单词\n",
      "crowd\n",
      "n. 群众,一伙;一堆,许多,大众\n",
      "v. 拥挤,挤满,挤进\n",
      "vt. 挤满,将...塞进;催促,催逼\n",
      "vi. 挤,拥挤,聚集\n",
      "\n",
      "翻译结束！\n"
     ]
    }
   ],
   "source": [
    "# -*- coding:utf-8 -*-\n",
    "from openpyxl import load_workbook\n",
    "from openpyxl import Workbook\n",
    "import json\n",
    "import sys\n",
    "from urllib.parse import urlparse, quote, urlencode, unquote\n",
    "from urllib.request import urlopen\n",
    "import re\n",
    " \n",
    "def fetch(query_str):\n",
    "    query = {'q': \"\".join(query_str)}   # list --> str: \"\".join(list)\n",
    "    url = 'https://fanyi.youdao.com/openapi.do?keyfrom=11pegasus11&key=*********&type=data&doctype=json&version=1.1&' + urlencode(query)\n",
    "    response = urlopen(url, timeout=3)\n",
    "    html = response.read().decode('utf-8')\n",
    "    return html\n",
    " \n",
    "def parse(html, num):\n",
    "    d = json.loads(html)\n",
    "    try:\n",
    "        if d.get('errorCode') == 0:\n",
    "            explains = d.get('basic').get('explains')\n",
    "            result = str(explains).replace('\\'', \"\").replace('[', \"\").replace(']', \"\")  #.replace真好用~\n",
    "            sheet.cell(row=num, column=2).value = result\n",
    "            num = num+1\n",
    "            for i in explains:\n",
    "                print(i)\n",
    "        else:\n",
    "            print('无法翻译!****')\n",
    "            sheet.cell(row = num, column = 2).value = ' '       #若无法翻译，则空出来\n",
    "            num = num + 1\n",
    "    except:\n",
    "        print('****翻译出错!')      #若无法翻译，则空出来\n",
    "        sheet.cell(row = num, column = 2).value = ' '\n",
    "        num = num + 1\n",
    " \n",
    "def main():\n",
    "    Sheet1 = ExcelFile['Sheet1']; num = 1\n",
    "    while(1):\n",
    "        word = Sheet1.cell(row = num+2, column = 1).value\n",
    "        if(word != None):\n",
    "            print('正在翻译第', end=''); print(num, end=''); print('个单词')\n",
    "            print(word)\n",
    "            parse(fetch(word), num)\n",
    "            num += 1\n",
    "            print()\n",
    "        else:\n",
    "            print('翻译结束！')\n",
    "            break\n",
    "    ExcelFile.close()\n",
    "    out.save('out.xlsx')\n",
    " \n",
    "if __name__ == '__main__':\n",
    "    ExcelFile = load_workbook('/Users/rongrong/Desktop/trybook.xlsx')      #输入文件\n",
    "    out = Workbook()\n",
    "    sheet = out.active\n",
    "    sheet.title = \"out\"\n",
    "    main()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
